﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;

namespace DAL
{
    /**
     * 
     * DataProvider lié aux Produits.
     * 
     **/
    public class ProductDataProvider : BaseDataProvider
    {
        private string table = "product"; // nom de la table
        private string id = "id_product"; // nom de la clé primaire

        public override string getTable()
        {
            return this.table;
        }

        public override string getId()
        {
            return this.id;
        }

        /**
         * 
         * Sauvegarde un produit
         * 
         **/
        public bool save(DataTable dataDT)
        {
            using (MySqlConnection conn = new MySqlConnection(this.connString))
            {
                conn.Open();

                DataRow row = dataDT.Rows[0];
                MySqlCommand cmd = null;

                if (row["id"].Equals(0)) // si l'id est null, c'est un nouveau produit
                {
                    cmd = new MySqlCommand("INSERT INTO " + getTable() + "(name, description, price, stock, category_id_category, created_at) VALUES(@name, @description, @price, @stock, @category_id_category, now())", conn);
                    cmd.Parameters.AddWithValue("@category_id_category", row["category_id_category"]);
                }
                else // sinon, c'est une mise à jour
                {
                    cmd = new MySqlCommand("UPDATE " + getTable() + " SET name = @name, description = @description, price = @price, stock = @stock WHERE " + getId() + " = @id", conn);
                    cmd.Parameters.AddWithValue("@id", row["id"]);
                }

                cmd.Parameters.AddWithValue("@name", row["name"]);
                cmd.Parameters.AddWithValue("@description", row["description"]);
                cmd.Parameters.AddWithValue("@price", row["price"]);
                cmd.Parameters.AddWithValue("@stock", row["stock"]);

                cmd.ExecuteNonQuery();
            }
            return true;
        }

        public DataTable findFromCategory(int category_id)
        {
            DataTable dt = new DataTable();

            using (MySqlConnection conn = new MySqlConnection(this.connString))
            {
                conn.Open();
                MySqlDataAdapter adpt = new MySqlDataAdapter();
                adpt.SelectCommand = new MySqlCommand("SELECT * FROM " + getTable() + " WHERE category_id_category = @id", conn);
                adpt.SelectCommand.Parameters.AddWithValue("@id", category_id);
                
                adpt.Fill(dt);
            }

            return dt;
        }

        public DataTable getLastProducts()
        {
            DataTable dt = new DataTable();

            using (MySqlConnection conn = new MySqlConnection(this.connString))
            {
                conn.Open();
                MySqlDataAdapter adpt = new MySqlDataAdapter();
                adpt.SelectCommand = new MySqlCommand("SELECT * FROM " + getTable() + " ORDER BY created_at DESC LIMIT 4", conn);
                adpt.Fill(dt);
            }

            return dt;
        }
    }
}
